﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class ThietLapDAL
    {
        public static void InsertThietLap(ThietLapPublic _tl)
        {
            ThietLap tl = new ThietLap();
            tl.MaPX = _tl.MaPX;
            //tl.MaQH = _tl.MaQH;
            //tl.MaT = _tl.MaT;
            Common.db.AddToThietLaps(tl);
            Common.db.SaveChanges();
        }
        public static ThietLapPublic LoadThietLap()
        {
            var thietlap = Common.db.ExecuteStoreQuery<ThietLapPublic>(@"select * from ThietLap");
            return thietlap.ToList()[0];
        }
        public static ThietLapPublic LoadThietLapByMaNK(int mank)
        {
            var tl = Common.db.ExecuteStoreQuery<ThietLapPublic>(@"select * from ThietLap 
where RIGHT(MaPX,2) = LEFT(right('0'+ cast("+mank+" as varchar(8)),8),2)");
            return tl.FirstOrDefault() ;
        }
        public static bool TestThietLap()
        {
            var tl = Common.db.ExecuteStoreQuery<ThietLapPublic>(@"select MaPX from ThietLap");
            if (tl.Count() > 0)
            {
                return false;
            }
            else
                return true;
        }
        public static string LoadDiaChi()
        {
            string chuoi = "select TenPhuongXa + ' - ' + TenQuanHuyen + ' - ' + TenTinh as DiaChi from PhuongXa px,QuanHuyen qh,Tinh t where px.MaQH=qh.MaQH and qh.MaT=t.MaT and MaPX in (select MaPX from ThietLap)";
            var tl = Common.db.ExecuteStoreQuery<string>(@chuoi);
            return tl.FirstOrDefault().ToString();
        }
        public static string LoadMaDiaChi()
        {
            string chuoi = @"
                                select right('0' + CAST(t.MaT as varchar),2) + RIGHT('0' + CAST(qh.MaQH as varchar),2) + RIGHT('0' + CAST(MaPX as varchar),2) as MaDiaChi 
                                from PhuongXa px, QuanHuyen qh, Tinh t 
                                where px.MaQH=qh.MaQH and qh.MaT=t.MaT and MaPX in (select MaPX from ThietLap);
                            ";
            var tl = Common.db.ExecuteStoreQuery<string>(@chuoi);
            return tl.FirstOrDefault().ToString();
        }
        public static string LoadDiaChiByMaNK(int MaNK)
        {
            string chuoi = "select TenPhuongXa + ' - ' + TenQuanHuyen + ' - ' + TenTinh as DiaChi from PhuongXa px,QuanHuyen qh,Tinh t where px.MaQH=qh.MaQH and qh.MaT=t.MaT and MaPX=left("+"'"+MaNK+"'"+",2)";
            var tl = Common.db.ExecuteStoreQuery<string>(@chuoi);
            return tl.FirstOrDefault().ToString();
        }
        public static string LoadDiaChiByMaDiaChi(int MaDC)
        {
            string chuoi = "select TenPhuongXa + ' - ' + TenQuanHuyen + ' - ' + TenTinh as DiaChi from PhuongXa px,QuanHuyen qh,Tinh t where px.MaQH=qh.MaQH and qh.MaT=t.MaT and MaPX = (right('0' + '" + MaDC + "',6))";
            var tl = Common.db.ExecuteStoreQuery<string>(@chuoi);
            return tl.FirstOrDefault();
        }
        public static string LoadDiaPhuong()
        {
            var tl = Common.db.ExecuteStoreQuery<string>("select upper(TenPhuongXa) from PhuongXa where MaPX=(select MaPX from ThietLap)");
            return tl.FirstOrDefault();
        }
        public static string LoadTenPhuongXa()
        {
            var tl = Common.db.ExecuteStoreQuery<string>("select TenPhuongXa from PhuongXa where MaPX in (select MaPX from ThietLap)");
            return tl.FirstOrDefault();
        }
     
    }
}
